✏️【本日實作紀錄：SQLite 資料庫整合與歷史紀錄 API 實作】

今天進入後端資料持久化（Persistence）階段，完成項目包含：

• 設計 SQLite 資料庫 Schema — 建立 prescriptions 與 medicines 兩張關聯資料表，儲存用藥解析紀錄與藥品明細。
• 重構 Flask 分析端點（/analyze-prescription） — 將 VLM 解析結果與 .mp3 語音檔路徑同步寫入 SQLite 資料庫。
• 實作歷史紀錄查詢 API（/prescriptions） — 提供前端（LINE Bot / Web 儀表板）抓取歷史用藥清單與詳細資訊的 RESTful 端點。

一、 為什麼需要後端 SQLite 資料庫？

前六天的實作中讓 API 接收藥袋照片、呼叫 VLM 解析並生成語音檔。但沒有資料庫的話，每次請求過後資料就隨記憶體釋放而消失。

將用藥紀錄寫入 SQLite 資料庫有幾個好處。長輩可隨時透過 LINE 查詢過去掃描過的藥單，家屬也能透過 Web 關懷儀表板 遠端掌握歷史用藥歷程。未來結合 LINE Messaging API 時，更可直接從資料庫調取紀錄進行自動化關懷推播給家屬。透過主外鍵（Foreign Key）設計，將「每一次的藥袋掃描」與「該藥袋內包含的多種藥品」清晰連結。

二、 編寫資料庫管理與 API 邏輯（app.py）

開啟專案根目錄下的 app.py，更新為以下完整程式碼：

import os
import json
import uuid
import sqlite3
from flask import Flask, request, jsonify, send_from_directory
from dotenv import load_dotenv
from google import genai
from google.genai import types
from PIL import Image
from gtts import gTTS

# 1. 載入環境變數與初始化
load_dotenv()
api_key = os.getenv("GEMINI_API_KEY")
if not api_key:
    raise ValueError("❌ 錯誤：找不到 GEMINI_API_KEY，請檢查 .env 設定！")

client = genai.Client(api_key=api_key)

app = Flask(__name__)
app.json.ensure_ascii = False  # 讓 Flask 回傳漂亮的繁體中文 JSON

# 設定檔案儲存目錄與資料庫路徑
AUDIO_DIR = os.path.join(os.getcwd(), 'static', 'audio')
DATABASE_PATH = os.path.join(os.getcwd(), 'prescription_vlm.db')
os.makedirs(AUDIO_DIR, exist_ok=True)

# 2. 資料庫初始化
def init_db():
    conn = sqlite3.connect(DATABASE_PATH)
    cursor = conn.cursor()
    
    # 建立藥袋紀錄表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS prescriptions (
            id TEXT PRIMARY KEY,
            spoken_summary TEXT NOT NULL,
            audio_url TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    # 建立藥品明細表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS medicines (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            prescription_id TEXT NOT NULL,
            name TEXT NOT NULL,
            type TEXT NOT NULL,
            frequency TEXT NOT NULL,
            dosage TEXT NOT NULL,
            timing TEXT NOT NULL,
            warning TEXT,
            FOREIGN KEY (prescription_id) REFERENCES prescriptions (id)
        )
    ''')
    
    conn.commit()
    conn.close()

# 初始化資料庫結構
init_db()

# 3. 定義 JSON Schema
prescription_schema = {
    "type": "OBJECT",
    "properties": {
        "spoken_summary": {
            "type": "STRING",
            "description": "適合唸給長輩聽的白話文總結，語氣溫柔親切。"
        },
        "medicines": {
            "type": "ARRAY",
            "items": {
                "type": "OBJECT",
                "properties": {
                    "name": {"type": "STRING", "description": "藥品中文名稱或商品名"},
                    "type": {"type": "STRING", "description": "口服或外用"},
                    "frequency": {"type": "STRING", "description": "服用頻率，如：每日二次"},
                    "dosage": {"type": "STRING", "description": "每次劑量，如：1顆、0.5顆"},
                    "timing": {"type": "STRING", "description": "吃藥時間點，如：飯後、睡前"},
                    "warning": {"type": "STRING", "description": "重要警語或注意事項，無則填空字串"}
                },
                "required": ["name", "type", "frequency", "dosage", "timing"]
            }
        }
    },
    "required": ["spoken_summary", "medicines"]
}

@app.route('/ping', methods=['GET'])
def ping():
    return jsonify({"status": "online", "service": "PrescriptionVLM Engine"}), 200

@app.route('/analyze-prescription', methods=['POST'])
def analyze_prescription():
    if 'image' not in request.files:
        return jsonify({"error": "未提供圖片檔案 (key 名稱應為 'image')"}), 400

    file = request.files['image']
    if file.filename == '':
        return jsonify({"error": "未選擇上傳的圖片"}), 400

    try:
        # 讀取影像並呼叫 VLM
        image = Image.open(file.stream)
        prompt = """
        你是一位專業且細心的藥師助手。請分析這張藥袋照片：
        1. 將藥品分類為口服或外用，並精準提取名稱、頻率、劑量與吃藥時間。
        2. 針對高齡長輩，撰寫一段溫柔白話的 spoken_summary，適合 TTS 語音播報。
        3. 若有重要注意事項，請填入 warning 欄位。
        """

        config = types.GenerateContentConfig(
            response_mime_type="application/json",
            response_schema=prescription_schema
        )

        response = client.models.generate_content(
            model='gemini-3.6-flash',
            contents=[image, prompt],
            config=config
        )

        result_data = json.loads(response.text)

        # 產生語音檔
        spoken_text = result_data.get("spoken_summary", "解析完成。")
        prescription_id = uuid.uuid4().hex[:8]
        filename = f"speech_{prescription_id}.mp3"
        filepath = os.path.join(AUDIO_DIR, filename)

        tts = gTTS(text=spoken_text, lang='zh-tw')
        tts.save(filepath)

        audio_url = f"/static/audio/{filename}"
        result_data['audio_url'] = audio_url
        result_data['id'] = prescription_id

        # 寫入 SQLite 資料庫
        conn = sqlite3.connect(DATABASE_PATH)
        cursor = conn.cursor()

        cursor.execute(
            "INSERT INTO prescriptions (id, spoken_summary, audio_url) VALUES (?, ?, ?)",
            (prescription_id, spoken_text, audio_url)
        )

        for med in result_data.get("medicines", []):
            cursor.execute(
                """INSERT INTO medicines 
                   (prescription_id, name, type, frequency, dosage, timing, warning) 
                   VALUES (?, ?, ?, ?, ?, ?, ?)""",
                (
                    prescription_id,
                    med.get("name"),
                    med.get("type"),
                    med.get("frequency"),
                    med.get("dosage"),
                    med.get("timing"),
                    med.get("warning", "")
                )
            )

        conn.commit()
        conn.close()

        return jsonify({
            "status": "success",
            "data": result_data
        }), 200

    except Exception as e:
        return jsonify({"error": f"伺服器處理失敗: {str(e)}"}), 500

@app.route('/prescriptions', methods=['GET'])
def get_prescriptions():
    """取得所有歷史用藥紀錄 API"""
    try:
        conn = sqlite3.connect(DATABASE_PATH)
        conn.row_factory = sqlite3.Row  # 讓查詢結果能用字典方式存取
        cursor = conn.cursor()

        cursor.execute("SELECT * FROM prescriptions ORDER BY created_at DESC")
        prescriptions = cursor.fetchall()

        history = []
        for p in prescriptions:
            cursor.execute("SELECT name, type, frequency, dosage, timing, warning FROM medicines WHERE prescription_id = ?", (p['id'],))
            meds = [dict(m) for m in cursor.fetchall()]

            history.append({
                "id": p['id'],
                "spoken_summary": p['spoken_summary'],
                "audio_url": p['audio_url'],
                "created_at": p['created_at'],
                "medicines": meds
            })

        conn.close()
        return jsonify({
            "status": "success",
            "data": history
        }), 200

    except Exception as e:
        return jsonify({"error": f"查詢失敗: {str(e)}"}), 500

@app.route('/static/audio/<filename>', methods=['GET'])
def get_audio(filename):
    return send_from_directory(AUDIO_DIR, filename)

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000, debug=True)

三、 API 測試與成果驗證

1. 啟動 Flask 後端服務

於 Terminal 執行，輸入（Ctrl+C 可取消執行 app.py）：

python app.py

啟動時會自動在專案根目錄下建立 prescription_vlm.db 資料庫檔案。

2. 測試分析與寫入 API

開啟新的 Terminal 分頁，上傳測試藥袋圖片：

curl -X POST http://127.0.0.1:5000/analyze-prescription \
  -F "image=@test_rx.jpg"

3. 測試歷史紀錄查詢 API

在 Terminal 執行 GET 請求，確認資料是否成功持久化：

curl http://127.0.0.1:5000/prescriptions

預期輸出結果

伺服器回傳包含 created_at 時間戳記、歷史解析 ID、白話文語音檔連結以及完整藥品清單的 JSON 陣列。

四、 版本控制與提交 GitHub

測試成功後，將 Day 07 的程式碼提交至 GitHub。注意 .db 資料庫檔不建議 Commit，已在 .gitignore 防護。

在 .gitignore 檔案末尾補上：

*.db

執行 Commit 與 Push：

git add .

git commit -m "保留雙引號 改填寫自己要記錄的標記 ex.鐵人賽第七天"

git push

五、 本日小結與明日預告

今天導入 SQLite 資料庫，實現用藥紀錄的持久化儲存與查詢功能。後端現在支援解析、語音生成、持久化儲存、歷史查詢這整個流程。

明天（Day 08）進行 LINE Messaging API 串接與家屬關懷推播通知。長輩掃描藥袋後，自動發送卡片訊息到家屬的 LINE 手機中。